const mysql = require('mysql2/promise');
const logger = require('../utils/logger');
const fs = require('fs').promises;
const path = require('path');
require('dotenv').config();

/* *******************
 * 配置管理
 ******************* */
async function generateTableConfigFromSQL() {
  try {
    const sqlPath = path.join(__dirname, 'init.sql');
    const content = await fs.readFile(sqlPath, 'utf8');
    
    // 清理SQL内容
    const cleanedContent = content
      .replace(/--.*$/gm, '') // 移除单行注释
      .replace(/\/\*[\s\S]*?\*\//g, ''); // 移除多行注释
    
    const tables = {};
    
    // 匹配CREATE TABLE语句
    const tableRegex = /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`(\w+)`\s*\(([\s\S]*?)\)\s*(?:ENGINE|$)/gi;
    let match;
    
    while ((match = tableRegex.exec(cleanedContent)) !== null) {
      const tableName = match[1];
      const tableContent = match[2];
      
      // 解析列定义
      const columns = [];
      const indexes = [];
      const columnTypes = {};
      
      // 分割并清理列定义
      const columnLines = tableContent
        .split(',')
        .map(line => line.trim())
        .filter(line => line.length > 0);
      
      for (const line of columnLines) {
        // 匹配列定义
        const columnMatch = line.match(/^`(\w+)`\s+([\w()]+)(\s+.*)?$/);
        if (columnMatch) {
          const columnName = columnMatch[1];
          const columnType = columnMatch[2].toLowerCase();
          columns.push(columnName);
          
          // 特殊类型检测
          if (columnType.includes('tinyint')) {
            columnTypes[columnName] = columnType;
          }
        }
        
        // 匹配主键定义
        const primaryKeyMatch = line.match(/PRIMARY\s+KEY\s*\(`[^`]+`\)/i);
        if (primaryKeyMatch) {
          indexes.push('PRIMARY');
          continue;
        }
        
        // 匹配其他索引定义
        const indexMatch = line.match(/(?:UNIQUE\s+)?(?:KEY|INDEX)\s+`(\w+)`/i);
        if (indexMatch && indexMatch[1]) {
          indexes.push(indexMatch[1]);
        }
      }
      
      // 检查是否需要初始化数据
      const hasInitData = cleanedContent.includes(`INSERT INTO \`${tableName}\``);
      
      tables[tableName] = {
        columns,
        indexes,
        columnTypes: Object.keys(columnTypes).length > 0 ? columnTypes : undefined,
        initData: hasInitData
      };
      
      logger.debug(`解析表 ${tableName} 配置:`, {
        columnsCount: columns.length,
        indexesCount: indexes.length,
        columnTypesCount: Object.keys(columnTypes).length,
        hasInitData
      });
    }
    
    return tables;
  } catch (error) {
    logger.error('从SQL文件生成表配置失败:', error);
    throw error;
  }
}

async function generateDatabaseConfig() {
  try {
    const sqlPath = path.join(__dirname, 'init.sql');
    const content = await fs.readFile(sqlPath, 'utf8');
    
    // 匹配数据库创建语句中的字符集和排序规则
    const dbConfigRegex = /CREATE\s+DATABASE\s+(?:IF\s+NOT\s+EXISTS\s+)?[\w_]+\s*(?:CHARACTER\s+SET\s+([^\s]+))?\s*(?:COLLATE\s+([^\s;]+))?/i;
    const match = content.match(dbConfigRegex);
    
    return {
      charset: match?.[1]?.toLowerCase() || 'utf8mb4',
      collation: match?.[2]?.toLowerCase() || 'utf8mb4_unicode_ci'
    };
  } catch (error) {
    logger.error('解析数据库配置失败:', error);
    return {
      charset: 'utf8mb4',
      collation: 'utf8mb4_unicode_ci'
    };
  }
}

// 初始化TABLE_CONFIG
let TABLE_CONFIG = {};

// 使用IIFE立即执行异步函数来初始化TABLE_CONFIG
(async () => {
  try {
    TABLE_CONFIG = await generateTableConfigFromSQL();
    logger.info('成功从SQL文件生成表配置');
  } catch (error) {
    logger.error('从SQL文件生成表配置失败，使用默认配置:', error);
    // 使用默认配置
    TABLE_CONFIG = {
      roles: {
        columns: ['id', 'name', 'code', 'description', 'status', 'is_deleted', 'created_at', 'updated_at'],
        indexes: ['PRIMARY', 'idx_code'],
        columnTypes: {
          status: 'tinyint(1)',
          is_deleted: 'tinyint(1)'
        },
        initData: true,
      },
      users: {
        columns: ['id', 'username', 'email', 'password', 'status', 'role_id', 'last_login', 'is_deleted', 'created_at', 'updated_at'],
        indexes: ['PRIMARY', 'idx_username', 'idx_email', 'idx_role_id', 'idx_is_deleted'],
        columnTypes: {
          status: 'tinyint(1)',
          is_deleted: 'tinyint(1)'
        },
        initData: true,
      },
      menus: {
        columns: ['id', 'parent_id', 'name', 'menu_type', 'path', 'component', 'icon', 'sort', 'status', 'is_deleted', 'created_at', 'updated_at'],
        indexes: ['PRIMARY', 'idx_parent_id'],
        columnTypes: {
          menu_type: 'tinyint(1)',
          status: 'tinyint(1)',
          is_deleted: 'tinyint(1)'
        },
        initData: true,
      },
      role_menus: {
        columns: ['id', 'role_id', 'menu_id', 'created_at'],
        indexes: ['PRIMARY', 'uk_role_menu'],
        initData: true,
      },
      login_logs: {
        columns: ['id', 'user_id', 'username', 'ip', 'user_agent', 'token', 'status', 'message', 'created_at'],
        indexes: ['PRIMARY', 'idx_user_id', 'idx_username', 'idx_created_at'],
        columnTypes: {
          status: 'tinyint(1)'
        },
      },
    };
  }
})();

/* *******************
 * SQL文件预加载
 ******************* */
let sqlStatements = [];

async function loadSQLFile() {
  try {
    const sqlPath = path.join(__dirname, 'init.sql');
    const content = await fs.readFile(sqlPath, 'utf8');
    
    // 改进SQL语句解析逻辑
    // 1. 移除注释
    let cleanedContent = content
      .replace(/--.*$/gm, '') // 移除单行注释
      .replace(/\/\*[\s\S]*?\*\//g, ''); // 移除多行注释
      
    // 2. 处理USE语句 - 我们将自己处理数据库选择
    cleanedContent = cleanedContent.replace(/USE\s+.*?;/gi, '');
    
    // 3. 分割SQL语句并过滤空语句
    sqlStatements = cleanedContent
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt.length > 0);
    
    // 4. 调试信息
    logger.info(`已加载 ${sqlStatements.length} 条SQL语句`);
    if (sqlStatements.length === 0) {
      logger.warn('未找到任何SQL语句，请检查init.sql文件');
    } else {
      // 记录前几条SQL语句的开头，用于调试
      logger.debug('SQL语句示例:', 
        sqlStatements.slice(0, 3).map(s => s.substring(0, 50) + '...'));
    }
  } catch (error) {
    logger.error('SQL文件加载失败:', error);
    throw error;
  }
}

/* *******************
 * 核心验证逻辑
 ******************* */
async function checkTableStructure(connection, tableName) {
  const config = TABLE_CONFIG[tableName];
  
  try {
    // 检查表是否存在
    const [tables] = await connection.query(
      `SELECT TABLE_NAME 
       FROM information_schema.TABLES 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?`,
      [process.env.DB_NAME, tableName]
    );
    
    if (tables.length === 0) {
      logger.info(`表 ${tableName} 不存在，需要创建`);
      return true;
    }
    
    const [columns] = await connection.query(
      `SELECT COLUMN_NAME, COLUMN_TYPE 
       FROM information_schema.COLUMNS 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?`,
      [process.env.DB_NAME, tableName]
    );

    // 列验证
    const missingColumns = config.columns.filter(
      col => !columns.some(dbCol => dbCol.COLUMN_NAME === col)
    );
    
    if (missingColumns.length) {
      logger.info(`表 ${tableName} 缺少列: ${missingColumns.join(', ')}`);
      return true;
    }

    // 列类型验证
    if (config.columnTypes) {
      let hasTypeIssue = false;
      for (const [col, type] of Object.entries(config.columnTypes)) {
        const dbColumn = columns.find(c => c.COLUMN_NAME === col);
        if (!dbColumn) continue; // 已在上面的缺少列检查中处理
        
        const dbType = dbColumn.COLUMN_TYPE.toLowerCase();
        const expectedType = type.toLowerCase();
        
        // 更宽松的类型匹配，只检查类型的主要部分
        // 例如 tinyint(1) 应该匹配 tinyint，不关心精度部分
        const dbTypeBase = dbType.split('(')[0];
        const expectedTypeBase = expectedType.split('(')[0];
        
        if (dbTypeBase !== expectedTypeBase) {
          logger.info(`表 ${tableName} 列 ${col} 类型不匹配: 期望 ${expectedType}, 实际 ${dbType}`);
          hasTypeIssue = true;
        }
      }
      
      if (hasTypeIssue) return true;
    }

    // 索引验证
    const [indexes] = await connection.query(
      `SELECT INDEX_NAME 
       FROM information_schema.STATISTICS 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?`,
      [process.env.DB_NAME, tableName]
    );

    if (!config.indexes) {
      return false; // 如果没有定义索引要求，则认为索引验证通过
    }

    const requiredIndexes = config.indexes.map(idx => idx.toLowerCase());
    const existingIndexes = indexes.map(idx => idx.INDEX_NAME.toLowerCase());

    // 特殊处理主键索引
    if (requiredIndexes.includes('primary') && !existingIndexes.includes('primary')) {
      logger.info(`表 ${tableName} 缺少主键索引`);
      return true;
    }

    const missingIndexes = requiredIndexes.filter(
      idx => idx !== 'primary' && !existingIndexes.includes(idx)
    );
    
    if (missingIndexes.length > 0) {
      logger.info(`表 ${tableName} 缺少索引: ${missingIndexes.join(', ')}`);
      return true;
    }
    
    logger.info(`表 ${tableName} 结构验证通过`);
    return false;
  } catch (error) {
    logger.error(`验证表 ${tableName} 结构时出错:`, error);
    return true; // 出错时保守处理，认为需要重建
  }
}

/* *******************
 * 数据库验证
 ******************* */
async function validateDatabaseStructure(connection) {
  try {
    const tablesToRebuild = [];

    // 获取SQL文件中的数据库配置
    const expectedConfig = await generateDatabaseConfig();
    
    // 数据库级验证
    const [databases] = await connection.query(
      `SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
       FROM information_schema.SCHEMATA 
       WHERE SCHEMA_NAME = ?`,
      [process.env.DB_NAME]
    );

    if (databases.length === 0) {
      logger.warn('数据库不存在，需要创建');
      return {
        isValid: false,
        tablesToRebuild: Object.keys(TABLE_CONFIG),
        charset: expectedConfig.charset,
        collation: expectedConfig.collation
      };
    }
    
    // 验证字符集和排序规则
    const actualCharset = databases[0].DEFAULT_CHARACTER_SET_NAME.toLowerCase();
    const actualCollation = databases[0].DEFAULT_COLLATION_NAME.toLowerCase();
    
    logger.info(`数据库配置 - 期望: charset=${expectedConfig.charset}, collation=${expectedConfig.collation}`);
    logger.info(`数据库配置 - 实际: charset=${actualCharset}, collation=${actualCollation}`);
    
    if (actualCharset !== expectedConfig.charset || actualCollation !== expectedConfig.collation) {
      logger.warn('数据库字符集或排序规则不匹配，需要重建数据库');
      return {
        isValid: false,
        tablesToRebuild: Object.keys(TABLE_CONFIG),
        charset: expectedConfig.charset,
        collation: expectedConfig.collation
      };
    }
    
    logger.info('数据库字符集和排序规则验证通过');

    // 表级验证
    logger.info('开始验证各个表结构...');
    for (const table of Object.keys(TABLE_CONFIG)) {
      const needsRebuild = await checkTableStructure(connection, table);
      if (needsRebuild) {
        logger.info(`表 ${table} 需要重建`);
        tablesToRebuild.push(table);
      } else {
        logger.info(`表 ${table} 结构正确，无需重建`);
      }
    }

    const isValid = tablesToRebuild.length === 0;
    if (isValid) {
      logger.info('所有表结构验证通过，无需重建');
    } else {
      logger.warn(`需要重建 ${tablesToRebuild.length} 个表: ${tablesToRebuild.join(', ')}`);
    }
    
    return { isValid, tablesToRebuild, charset: expectedConfig.charset, collation: expectedConfig.collation };
  } catch (error) {
    logger.error('验证数据库结构时出错:', error);
    return { 
      isValid: false, 
      tablesToRebuild: [],
      charset: 'utf8mb4',
      collation: 'utf8mb4_unicode_ci'
    };
  }
}

/* *******************
 * 重建逻辑
 ******************* */
async function rebuildTable(connection, tableName) {
  const safeTableName = mysql.escapeId(tableName);

  try {
    // 禁用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 0');

    // 删除表
    await connection.query(`DROP TABLE IF EXISTS ${safeTableName}`);
    logger.info(`已删除表 ${tableName}`);

    // 创建表 - 修改查找逻辑以匹配实际SQL格式
    const createStmt = sqlStatements.find(stmt => {
      const lowerStmt = stmt.trim().toLowerCase();
      return (
        lowerStmt.includes(`create table`) && 
        (lowerStmt.includes(`\`${tableName.toLowerCase()}\``) || 
        lowerStmt.includes(`${tableName.toLowerCase()}`))
      );
    });
    
    if (!createStmt) {
      logger.error(`未找到 ${tableName} 的建表语句，可用的SQL语句：`, 
        sqlStatements.map(s => s.substring(0, 50) + '...').join('\n'));
      throw new Error(`未找到 ${tableName} 的建表语句`);
    }
    
    await connection.query(createStmt);
    logger.info(`已重建表 ${tableName}`);

    // 重新启用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
  } catch (error) {
    // 确保在出错时也重新启用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
    logger.error(`重建表 ${tableName} 失败:`, error);
    throw error;
  }
}

/* *******************
 * 初始化数据
 ******************* */
async function initializeData(connection) {
  try {
    // 禁用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 0');
    
    // 查找所有初始化数据的SQL语句
    const insertStatements = sqlStatements.filter(stmt => 
      stmt.trim().toLowerCase().startsWith('insert into')
    );
    
    if (insertStatements.length === 0) {
      logger.warn('未找到初始化数据的SQL语句');
      return;
    }
    
    // 按照表的依赖关系顺序执行初始化数据
    const tableOrder = ['roles', 'users', 'menus', 'role_menus'];
    
    for (const table of tableOrder) {
      const tableInserts = insertStatements.filter(stmt => 
        stmt.toLowerCase().includes(`insert into \`${table}\``) || 
        stmt.toLowerCase().includes(`insert into ${table}`)
      );
      
      if (tableInserts.length > 0) {
        logger.info(`正在初始化表 ${table} 的数据...`);
        
        // 检查表中是否已有数据
        const [rows] = await connection.query(`SELECT COUNT(*) as count FROM ${mysql.escapeId(table)}`);
        if (rows[0].count > 0) {
          logger.info(`表 ${table} 已有数据，跳过初始化`);
          continue;
        }
        
        // 根据不同表类型进行特殊处理
        for (const insertStmt of tableInserts) {
          try {
            // 提取插入语句中的关键字段值
            let skipInsert = false;
            
            if (table === 'roles') {
              // 从INSERT语句中提取name值
              const nameMatch = insertStmt.match(/VALUES\s*\([^)]*'([^']*)'[^)]*\)/i);
              if (nameMatch) {
                const name = nameMatch[1];
                const [existing] = await connection.query(
                  'SELECT id FROM roles WHERE name = ?',
                  [name]
                );
                if (existing.length > 0) {
                  logger.info(`角色 ${name} 已存在，跳过插入`);
                  skipInsert = true;
                }
              }
            } else if (table === 'users') {
              // 从INSERT语句中提取username值
              const usernameMatch = insertStmt.match(/VALUES\s*\([^,]*,[^,]*'([^']*)'[^)]*\)/i);
              if (usernameMatch) {
                const username = usernameMatch[1];
                const [existing] = await connection.query(
                  'SELECT id FROM users WHERE username = ?',
                  [username]
                );
                if (existing.length > 0) {
                  logger.info(`用户 ${username} 已存在，跳过插入`);
                  skipInsert = true;
                }
              }
            } else if (table === 'menus') {
              // 从INSERT语句中提取path值
              const pathMatch = insertStmt.match(/VALUES\s*\([^,]*,[^,]*,[^,]*,[^,]*,'([^']*)'[^)]*\)/i);
              if (pathMatch) {
                const path = pathMatch[1];
                const [existing] = await connection.query(
                  'SELECT id FROM menus WHERE path = ?',
                  [path]
                );
                if (existing.length > 0) {
                  logger.info(`菜单路径 ${path} 已存在，跳过插入`);
                  skipInsert = true;
                }
              }
            }
            
            // 如果不需要跳过，则执行插入
            if (!skipInsert) {
              await connection.query(insertStmt);
              logger.info(`成功执行插入语句: ${insertStmt.substring(0, 100)}...`);
            }
          } catch (err) {
            logger.warn(`插入${table}表数据时出现警告: ${err.message}`);
            // 继续处理下一条数据
            continue;
          }
        }
        logger.info(`表 ${table} 数据初始化完成`);
      }
    }
    
    // 重新启用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
    logger.info('所有表数据初始化完成');
  } catch (error) {
    // 确保在出错时也重新启用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
    logger.error('初始化数据失败:', error);
    throw error;
  }
}

/* *******************
 * 主流程
 ******************* */
async function initDatabase() {
  let connection;

  try {
    logger.info('开始数据库初始化...');
    
    // 加载SQL文件
    await loadSQLFile();

    // 创建基础连接
    logger.info('正在连接数据库服务器...');
    connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      port: process.env.DB_PORT,
    });
    logger.info('数据库服务器连接成功');

    // 验证结构
    logger.info('验证数据库结构...');
    const { isValid, tablesToRebuild, charset, collation } = await validateDatabaseStructure(connection);

    // 创建数据库（如果需要）
    logger.info('检查数据库是否存在...');
    const [databases] = await connection.query(
      `SELECT SCHEMA_NAME 
       FROM information_schema.SCHEMATA 
       WHERE SCHEMA_NAME = ?`,
      [process.env.DB_NAME]
    );

    let isNewDatabase = false;
    if (databases.length === 0) {
      logger.info(`数据库 ${process.env.DB_NAME} 不存在，正在创建...`);
      await connection.query(
        `CREATE DATABASE ${mysql.escapeId(process.env.DB_NAME)} 
         CHARACTER SET ${charset} 
         COLLATE ${collation}`
      );
      logger.info(`数据库 ${process.env.DB_NAME} 创建成功`);
      isNewDatabase = true;
    } else if (!isValid) {
      // 如果数据库存在但配置不匹配，重建数据库
      logger.info(`重建数据库 ${process.env.DB_NAME} 使用新的字符集和排序规则...`);
      await connection.query(`DROP DATABASE ${mysql.escapeId(process.env.DB_NAME)}`);
      await connection.query(
        `CREATE DATABASE ${mysql.escapeId(process.env.DB_NAME)} 
         CHARACTER SET ${charset} 
         COLLATE ${collation}`
      );
      isNewDatabase = true;
    }

    logger.info(`切换到数据库 ${process.env.DB_NAME}`);
    await connection.query(`USE ${mysql.escapeId(process.env.DB_NAME)}`);

    // 如果是新创建的数据库或者结构无效，需要重建表
    if (isNewDatabase) {
      logger.info('新创建的数据库，需要创建所有表并初始化数据');
      // 创建所有表
      await Promise.all(
        Object.keys(TABLE_CONFIG).map(table => rebuildTable(connection, table))
      );
      
      // 初始化数据
      await initializeData(connection);
    } else if (!isValid) {
      // 重建表（并行处理无依赖的表）
      logger.info(`需要重建的表: ${tablesToRebuild.join(', ')}`);
      await Promise.all(
        tablesToRebuild.map(table => rebuildTable(connection, table))
      );
      
      // 初始化数据
      await initializeData(connection);
    } else {
      // 检查是否需要初始化数据
      logger.info('数据库结构完整，检查是否需要初始化数据...');
      
      // 检查是否有数据
      const hasData = await checkIfDataExists(connection);
      if (!hasData) {
        logger.info('数据库表结构正确但缺少初始数据，执行数据初始化');
        await initializeData(connection);
      } else {
        logger.info('数据库结构和数据都已完整，无需任何操作');
      }
    }

    logger.info('数据库初始化完成');
  } catch (error) {
    logger.error('数据库初始化失败:', error);
    throw error;
  } finally {
    if (connection) {
      logger.info('关闭数据库连接');
      await connection.end();
    }
  }
}

/* *******************
 * 检查是否有数据
 ******************* */
async function checkIfDataExists(connection) {
  try {
    // 检查关键表是否有数据
    const tables = ['roles', 'users', 'menus'];
    
    for (const table of tables) {
      const [rows] = await connection.query(`SELECT COUNT(*) as count FROM ${mysql.escapeId(table)}`);
      if (rows[0].count === 0) {
        logger.info(`表 ${table} 没有数据`);
        return false;
      }
    }
    
    logger.info('所有关键表都有数据');
    return true;
  } catch (error) {
    logger.error('检查数据时出错:', error);
    return false; // 出错时保守处理，认为没有数据
  }
}

module.exports = {
  initDatabase
}; 